Database Performance Recommendations

To help you get the best performance from your database we have collected together our best practice recommendations for database settings and options. The best settings for one institution are not always best for all, so we have included some guidance to help you tailor your settings to your specific setup.

You may also wish to refer to our Application Server Performance Recommendations

Index and Statistics Maintenance

It is important to rebuild and reorganise indexes, as well as update table statistics to ensure that SQL Server performance does not degrade over time.

For example, Tribal schedule regular jobs based on scripts available here.

Tribal can assist with the setup of scheduled database maintenance jobs. This can be done as a one-off chargeable activity that can be requested through the self-service portal. Alternatively, if you would like Tribal to setup jobs and manage these on your behalf as part of our MITS service, please contact your account manager.

Settings Applied on Upgrade

The following ebs database configuration changes are automatically made during the upgrade process:

If the ebs database Compatibility Level is less than the SQL Server version, it will be reset to the native SQL Server version.
- E.g. if using SQL Server 2017 (140) and the EBS database Compatibility Level is set to SQL Server 2016 (130), the upgrade will reset it SQL Server 2017 (140).
For SQL Server 2016, 2017, and 2019 enable Legacy Cardinality Estimation.

From release 4.42, ebs is now supported running on SQL Server 2022. This has been trialled with the default 'out of the box' settings

• Legacy Cardinality DISABLED (OFF)

• T-SQL Scalar UDF In-lining Enabled (ON)

We would encourage all customers to feedback performance issues to Tribal, but particularly the impact of the various settings on SQL Server 2022 so that we can provide advice on this version

Recommended ebs Database Settings

Note: Please be aware that changing any of these settings can have a dramatic impact on the performance and stability of the ebs system. Our recommended best practice when evaluating performance it to only change one setting at a time. Then carefully monitor the impact of this change in isolation.

For further detailed information on each of these settings, and the potential impact of any changes, please refer to Microsoft documentation for your SQL Server version.

Setting or Option (Type or Level)	Description	Recommended Settings	Comments
Compatibility Level (Database Property) Microsoft Learn	Sets the SQL Server version the ebs database is made compatible with. This is in respect to baseline optimiser behaviour and available functionality.	Native (match to the SQL Server version) E.g. 2016 = 130 2017 = 140 2019 = 150 2022 = 160	It is important to keep this setting Native wherever possible so that ebs application code can make use of the latest optimiser improvements, functionality and any other benefits made available by default on the SQL Server version. Downgrading the Compatibility Level is often seen as a quick fix for performance issues as SQL Server 2016 generally appears to perform better than SQL Server 2019. However, in our own experience this is more to do with the impact of other SQL Server and environment settings or ebs application code optimisation issues, so downgrading Compatibility Level from Native is not a recommended solution.
Legacy Cardinality Estimation (Database Scoped Configuration) Microsoft Learn	The SQL Server Query Optimizer is cost-based. One of the main factors in determining the cost of executing a query plan is assessment of the total number of rows processed at each level, referred to as the cardinality of the plan. This option indicates whether to use legacy SQL Server 2012 (110) cardinality estimation rules in assessing the cost of a query plan. Note that this option works in conjunction with Compatibility Level to determine the optimiser behaviour.	2016, 2017, and 2019 Enable (ON) 2022 Disable (OFF)	ebs application code has been optimised for the ‘legacy’ cardinality rules for many years, and the weight of evidence suggests this option should continue to be Enabled. This gives predictable query results in conjunction when combined with Native Compatibility Level. Historically, switching Compatibility Level to SQL Server 2012 (110) achieved the same objective, but this is no longer supported for ebs. We hoped that many performance issues would be resolved in SQL Server 2019, however, on balance, we continue to advise to Enable this setting on this SQL Server version. For SQL Server 2022, we are advising to Disable this setting, and encouraging customers to give feedback on the impact.
T-SQL Scalar UDF In-lining (Database Scoped Configuration) Microsoft Learn	A new SQL Server 2019 optimiser feature, the goal of which is to improve performance of queries that invoke T-SQL Scalar UDFs (User Defined Functions), where row-by-row UDF execution is the main bottleneck in performance degradation.	Dependent on the Cumulative Update (CU) applied to SQL Server 2019 <= CU16 - Disable (OFF) >= CU17 - Evaluation SQL Server 2022 – Enable (evaluation)	For CU16 and earlier SQL Server 2019 installations, we recommend disabling this feature. We have seen numerous issues, including ebs application code breakages (one reported by Tribal & fixed by Microsoft) as well as extremely poor performance of some queries. For CU17 and beyond, our internal testing has shown a mixture of positive and negative impacts of enabling this feature. We therefore have no current recommendation and invite customers to evaluate themselves the impact of this setting. For SQL Server 2022, we are advising to Enable this setting, and encouraging customers to give feedback on the impact.
Cost Threshold of Parallelism (SQL Server Config – Advanced Options) Microsoft Learn	Specifies the cost threshold of a query plan at which SQL Server considers choosing a parallel execution plan using worker and co-ordinator threads. Actual parallel execution is of course dependent on other parameters such as number of logical CPUs available to the SQL Server instance and Max Degree of Parallelism configured.	300	The default value for this configuration setting is just 5, so even the lightest of queries will be considered to use parallel execution, needlessly and expensively invoking threads, resulting in co-ordination overheads. This is an advanced option that should be evaluated and adjusted with care to avoid detrimental results. Based on general forum publications, advisories and observations made for hosted customers, we are currently recommending a ballpark setting of 300 to evaluate the impact of changing this setting from its default, so only the most expensive of ebs application code queries will qualify for parallel execution plans.
Max Degree of parallelism (MAXDOP) (SQL Server Config – Advanced Options) Microsoft Learn - Server Config (Database Scoped Configuration) Microsoft Learn - MAXDOP	Controls the maximum number of logical processors used to run a single statement in a parallel query execution plan. MAXDOP can be configured at individual database level overriding the SQL Server instance level configuration.	Leave as default	From SQL Server 2019, a default value is recommended based on the number of logical processors available during the installation process. This an advanced option that should be evaluated and adjusted with care to avoid detrimental results. This may be safer to evaluate at the individual ebs database level. We are still evaluating the impact of this option, so our current recommendation is to leave it at the default value.

Setting or Option

(Type or Level)

Description

Recommended Settings

Comments

Compatibility Level

(Database Property)

Microsoft Learn

Sets the SQL Server version the ebs database is made compatible with. This is in respect to baseline optimiser behaviour and available functionality.

Native

(match to the SQL Server version)

E.g. 2016 = 130

2017 = 140

2019 = 150

2022 = 160

It is important to keep this setting Native wherever possible so that ebs application code can make use of the latest optimiser improvements, functionality and any other benefits made available by default on the SQL Server version.

Downgrading the Compatibility Level is often seen as a quick fix for performance issues as SQL Server 2016 generally appears to perform better than SQL Server 2019.

However, in our own experience this is more to do with the impact of other SQL Server and environment settings or ebs application code optimisation issues, so downgrading Compatibility Level from Native is not a recommended solution.

Legacy Cardinality Estimation

(Database Scoped Configuration)

Microsoft Learn

The SQL Server Query Optimizer is cost-based. One of the main factors in determining the cost of executing a query plan is assessment of the total number of rows processed at each level, referred to as the cardinality of the plan.

This option indicates whether to use legacy SQL Server 2012 (110) cardinality estimation rules in assessing the cost of a query plan.

Note that this option works in conjunction with Compatibility Level to determine the optimiser behaviour.

2016, 2017, and 2019

Enable (ON)

2022

Disable (OFF)

ebs application code has been optimised for the ‘legacy’ cardinality rules for many years, and the weight of evidence suggests this option should continue to be Enabled. This gives predictable query results in conjunction when combined with Native Compatibility Level.

Historically, switching Compatibility Level to SQL Server 2012 (110) achieved the same objective, but this is no longer supported for ebs.

We hoped that many performance issues would be resolved in SQL Server 2019, however, on balance, we continue to advise to Enable this setting on this SQL Server version.

For SQL Server 2022, we are advising to Disable this setting, and encouraging customers to give feedback on the impact.

T-SQL Scalar UDF In-lining

(Database Scoped Configuration)

Microsoft Learn

A new SQL Server 2019 optimiser feature, the goal of which is to improve performance of queries that invoke T-SQL Scalar UDFs (User Defined Functions), where row-by-row UDF execution is the main bottleneck in performance degradation.

Dependent on the Cumulative Update (CU) applied to SQL Server 2019

<= CU16 - Disable (OFF)

>= CU17 - Evaluation

SQL Server 2022 – Enable (evaluation)

For CU16 and earlier SQL Server 2019 installations, we recommend disabling this feature. We have seen numerous issues, including ebs application code breakages (one reported by Tribal & fixed by Microsoft) as well as extremely poor performance of some queries.

For CU17 and beyond, our internal testing has shown a mixture of positive and negative impacts of enabling this feature. We therefore have no current recommendation and invite customers to evaluate themselves the impact of this setting.

For SQL Server 2022, we are advising to Enable this setting, and encouraging customers to give feedback on the impact.

Cost Threshold of Parallelism

(SQL Server Config – Advanced Options)

Microsoft Learn

Specifies the cost threshold of a query plan at which SQL Server considers choosing a parallel execution plan using worker and co-ordinator threads.

Actual parallel execution is of course dependent on other parameters such as number of logical CPUs available to the SQL Server instance and Max Degree of Parallelism configured.

300

The default value for this configuration setting is just 5, so even the lightest of queries will be considered to use parallel execution, needlessly and expensively invoking threads, resulting in co-ordination overheads.

This is an advanced option that should be evaluated and adjusted with care to avoid detrimental results.

Based on general forum publications, advisories and observations made for hosted customers, we are currently recommending a ballpark setting of 300 to evaluate the impact of changing this setting from its default, so only the most expensive of ebs application code queries will qualify for parallel execution plans.

Max Degree of parallelism (MAXDOP)

(SQL Server Config – Advanced Options)

Microsoft Learn - Server Config

(Database Scoped Configuration)

Microsoft Learn - MAXDOP

Controls the maximum number of logical processors used to run a single statement in a parallel query execution plan.

MAXDOP can be configured at individual database level overriding the SQL Server instance level configuration.

Leave as default

From SQL Server 2019, a default value is recommended based on the number of logical processors available during the installation process.

This an advanced option that should be evaluated and adjusted with care to avoid detrimental results. This may be safer to evaluate at the individual ebs database level.

We are still evaluating the impact of this option, so our current recommendation is to leave it at the default value.